use sqlx::{MySqlPool, Row};

use crate::model::test::Test;

pub async fn get_by_name(conn: &MySqlPool, name: &String) -> Vec<Test> {
    let sql = match name == "" {
        true => "select id,name,age from test",
        false => "select id,name,age from test where name = ?"
    };
    let rows = sqlx::query(sql)
        .bind(name)
        .map(|row: sqlx::mysql::MySqlRow| {
            Test { 
                id: row.get("id"), 
                name: row.get("name"), 
                age: row.get("age") 
            }
        })
        .fetch_all(conn).await.unwrap();
    rows
}

pub async fn add_data(conn: &MySqlPool, test: Test) -> bool {     
    let result = sqlx::query("insert into test(name, age) values(?,?)")
        .bind(test.name)
        .bind(test.age)
        .execute(conn).await.expect("数据库插入发送异常");
    result.rows_affected() != 0
}

pub async fn put_data(conn: &MySqlPool, test: Test) -> bool {
    let result = sqlx::query("UPDATE test SET name=?, age = ? WHERE id=?")
        .bind(test.name)
        .bind(test.age)
        .bind(test.id)
        .execute(conn).await.unwrap();
    result.rows_affected() != 0
}

pub async fn del_data(conn: &MySqlPool, id: &i32) -> bool {
    let result = sqlx::query("DELETE FROM test WHERE id=?")
        .bind(id)
        .execute(conn).await.unwrap();
    result.rows_affected() != 0
} 

pub async fn find_by_id(conn: &MySqlPool, id: &i32) -> Test {
    let res = sqlx::query("select id,name,age from test where id = ?")
        .bind(id)
        .fetch_one(conn).await;
    match res {
        Ok(row) => Test {
            id: row.get("id"),
            name: row.get("name"),
            age: row.get("age"),
        },
        Err(_) => Test { id: Some(0), name: None, age: None }
    }
    
}